//
//  DataBase.m
//  Lesson_UI_MySql
//
//  Created by dllo on 15/7/10.
//  Copyright (c) 2015年 lanou. All rights reserved.
//

#import "DataBase.h"

static DataBase * dataBaseHandle = nil;
static DataBase * dataBaseHandle1 = nil;

@implementation DataBase

+(DataBase *)shareDataBaseHandle
{
    static dispatch_once_t onceToken;
    dispatch_once(&onceToken, ^{
        dataBaseHandle = [[DataBase alloc]init];
    });
    return dataBaseHandle;
}


#pragma mark-开启数据库
//创建一个数据库对象
static sqlite3 * db;

-(void)openDB
{
    
//    查找沙盒
    NSString * filePath = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES).lastObject;
//
    NSString * sqlitePath = [filePath stringByAppendingString:@"/menu.sqlite"];
    //NSLog(@"文件路径 = %@",sqlitePath);
    
    
    if (db != nil) {
        //NSLog(@"数据库已经打开了");
        return;
    }
    
    
//    打开数据库的方法  参数1:路径  参数2:数据库对象
    int result = sqlite3_open([sqlitePath UTF8String], &db);
    
    if (result == SQLITE_OK) {
        //NSLog(@"数据库开启成功");
    }else{
        //NSLog(@"数据库开启失败");
    }
}



#pragma mark-  创建数据库表格
-(void)creatTable
{
    
//    创建建表的sql语句
    NSString * createSql = @"CREATE TABLE IF NOT EXISTS menuList(number INTEGER PRIMARY KEY AUTOINCREMENT,title TEXT,name TEXT,DBpic TEXT,tags TEXT,ingredients TEXT,burden TEXT, steps BLOB)";
    
    
//    执行sql语句
    int result = sqlite3_exec(db, [createSql UTF8String], NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        //NSLog(@"创建表成功");
    }else{
        //NSLog(@"创建表失败");
    }
}




#pragma mark- 往数据库表格中插入菜谱信息
-(void)insertCookInfo:(CookModel *)cook
{
    NSData *arrData = [NSKeyedArchiver archivedDataWithRootObject:cook.steps];
//    //NSLog(@"arrData:%@", arrData);
    
//    创建插入菜谱信息的sql语句
    NSString * insertSql = [NSString stringWithFormat:@"INSERT INTO menuList(title,name,DBpic,tags,ingredients,burden, steps)VALUES('%@','%@','%@','%@','%@', '%@', ?)",cook.title,cook.name,cook.DBpic,cook.tags,cook.ingredients,cook.burden];
    
//    [db executeUpdate: @"insert into menuList (steps) values (?);",arrData];
    
    sqlite3_stmt *insert_statement = nil;
    if (sqlite3_prepare_v2(db, [insertSql UTF8String], -1, &insert_statement, NULL) == SQLITE_OK) {
        sqlite3_bind_blob(insert_statement, 1, [arrData bytes],[arrData length], NULL);
        if(sqlite3_step(insert_statement) != SQLITE_DONE) {
            //NSLog(@"Db error %s", sqlite3_errmsg(db));
        }
    } else {
        //NSLog(@"Db error %s", sqlite3_errmsg(db));
    }
    sqlite3_finalize(insert_statement);
    

  
//  简单数据库
//  执行sql语句
//    int result = sqlite3_exec(db, [insertSql UTF8String], NULL, NULL, NULL);
//    if (result != SQLITE_OK) {
//        //NSLog(@"插入信息失败");
//    }
    
    
}



#pragma mark- 更新菜谱信息
-(void)updateCookInfo:(CookModel *)cook number:(NSInteger)number
{
    
//    创建更新菜谱信息的sql语句
    NSString * updateSql = [NSString stringWithFormat:@"UPDATE menuList SET title = '%@',name = '%@',DBpic = '%@',tags = '%@',ingredients = '%@',burden = '%@' WHERE number = '%ld'",cook.title,cook.name,cook.DBpic,cook.tags,cook.ingredients,cook.burden,number];
    
//    执行sql语句
    int result = sqlite3_exec(db, [updateSql UTF8String], NULL, NULL, NULL);
    if (result != SQLITE_OK) {
        //NSLog(@"更新失败");
    }
    
}




#pragma mark- 删除一个菜谱信息
-(void)deleteCookInfo:(NSInteger)number
{
    
//    创建删除菜谱信息的sql语句
    NSString * deleteSql = [NSString stringWithFormat:@"DELETE FROM menuList WHERE number = '%ld'",number];
//    执行sql语句
    int result = sqlite3_exec(db, [deleteSql UTF8String], NULL, NULL, NULL);
    if (result != SQLITE_OK) {
        //NSLog(@"删除失败");
    }
}


#pragma mark- 查询所有菜谱信息
-(NSMutableArray *)selectCook
{
    
//    创建查询菜谱信息的sql语句
    NSString * selectSql = @"SELECT * FROM menuList";
//    创建数据库指针对象
    sqlite3_stmt * stmt = nil;
//   将数据库对象、SQL语句、指针对象关联在一起
    /*
     参数1: 数据库对象
     参数2: 查询语句
     参数3: 查询语句字数限制（－1为不限制）
     参数4: 数据库指针对象
     参数5: nil
    */
    int result = sqlite3_prepare_v2(db, [selectSql UTF8String], -1, &stmt, nil);
    
//    创建存放菜谱信息的大数组
    NSMutableArray * array = [[NSMutableArray alloc]init];
    
    if (result != SQLITE_OK) {
        //NSLog(@"查询失败");
    }
//    循环遍历查询数据库表格每一行
    while (sqlite3_step(stmt) == SQLITE_ROW) {
//        取出每一列数据
        int number = sqlite3_column_int(stmt, 0);


        const unsigned char * title = sqlite3_column_text(stmt, 1);
        const unsigned char * name = sqlite3_column_text(stmt, 2);

        const unsigned char * DBpic = sqlite3_column_text(stmt, 3);
        const unsigned char * tags = sqlite3_column_text(stmt, 4);
        const unsigned char * ingredients = sqlite3_column_text(stmt, 5);
        const unsigned char * burden = sqlite3_column_text(stmt, 6);
        
        const void *op = sqlite3_column_blob(stmt, 7);
        int size = sqlite3_column_bytes(stmt,7);
        NSData *data = [[NSData alloc]initWithBytes:op length:size];

        
        NSString * titleString = [NSString stringWithUTF8String:(const char *)title];
        NSString * nameString = [NSString stringWithUTF8String:(const char *)name];
        NSString * DBpicString = [NSString stringWithUTF8String:(const char *)DBpic];
        NSString * tagsString = [NSString stringWithUTF8String:(const char *)tags];
        NSString * ingredientsString = [NSString stringWithUTF8String:(const char *)ingredients];
        NSString * burdenString = [NSString stringWithUTF8String:(const char *)burden];
        NSArray *stepsArr = [NSKeyedUnarchiver unarchiveObjectWithData:data];
        
        CookModel * coo = [[CookModel alloc]init];
        coo.number = number;
        coo.title = titleString;
        coo.name = nameString;
        coo.DBpic = DBpicString;
        coo.tags = tagsString;
        coo.ingredients = ingredientsString;
        coo.burden = burdenString;
        coo.steps = stepsArr;
      
        
//        将菜谱信息model类存入大数组
        [array addObject:coo];
    }
    sqlite3_finalize(stmt);
    return array;
}


#pragma mark- 关闭数据库
-(void)closeDB
{
    
    int result = sqlite3_close(db);
    db = nil;
    if (result != SQLITE_OK) {
        //NSLog(@"关闭数据库");
    }
}








+(DataBase *)KnowledgeDataBaseHandle
{
    static dispatch_once_t onceToken;
    dispatch_once(&onceToken, ^{
        dataBaseHandle1 = [[DataBase alloc]init];
    });
    return dataBaseHandle1;
}


#pragma mark-开启数据库
//创建一个数据库对象
static sqlite3 * db1;

-(void)openKnowledgeDB
{
    
    //    查找沙盒
    NSString * filePath = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES).lastObject;
    //
    NSString * sqlitePath = [filePath stringByAppendingString:@"/knowledge.sqlite"];
    //NSLog(@"文件路径 = %@",sqlitePath);
    
    
    if (db1 != nil) {
        //NSLog(@"数据库已经打开了");
        return;
    }
    
    
    //    打开数据库的方法  参数1:路径  参数2:数据库对象
    int result = sqlite3_open([sqlitePath UTF8String], &db1);
    
    if (result == SQLITE_OK) {
        //NSLog(@"数据库开启成功");
    }else{
        //NSLog(@"数据库开启失败");
    }
}



#pragma mark-  创建数据库表格
-(void)creatKnowledgeTable
{
    
    //    创建建表的sql语句
    NSString * createSql = @"CREATE TABLE IF NOT EXISTS knowledgeList(number INTEGER PRIMARY KEY AUTOINCREMENT,title TEXT,img TEXT,content TEXT,code TEXT,html TEXT)";
    
    //    执行sql语句
    int result = sqlite3_exec(db1, [createSql UTF8String], NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        //NSLog(@"创建表成功");
    }else{
        //NSLog(@"创建表失败");
    }
}




#pragma mark- 往数据库表格中插入知识信息
-(void)insertKnowledgeInfo:(KnowledgeModel *)knowledge
{
    
    //    创建插入知识信息的sql语句
    NSString * insertSql = [NSString stringWithFormat:@"INSERT INTO knowledgeList(title,img,content,code,html)VALUES('%@','%@','%@','%@','%@')",knowledge.title,knowledge.img,knowledge.content,knowledge.code,[knowledge.html stringByReplacingOccurrencesOfString:@"setTimeout(\"$.get('__ZHISHI__/nousStat',{code:'\"+$code+\"',site:'\"+$site+\"'})\",7000);" withString:@""]];
    
    //  执行sql语句
    char *err;
    int result = sqlite3_exec(db1, [insertSql UTF8String], NULL, NULL, &err);
    if (result != SQLITE_OK) {
        //NSLog(@"插入信息失败");
    }
}



#pragma mark- 更新知识信息
-(void)updateKnowledgeInfo:(KnowledgeModel *)knowledge number:(NSInteger)number
{
    
    //    创建更新知识信息的sql语句
    NSString * updateSql = [NSString stringWithFormat:@"UPDATE knowledgeList SET title = '%@',img = '%@',content = '%@',code = '%@',html = '%@' WHERE number = '%ld'",knowledge.title,knowledge.img,knowledge.content,knowledge.code,knowledge.html,number];
    
    //    执行sql语句
    int result = sqlite3_exec(db1, [updateSql UTF8String], NULL, NULL, NULL);
    if (result != SQLITE_OK) {
        //NSLog(@"更新失败");
    }
    
}




#pragma mark- 删除一个知识信息
-(void)deleteKnowledgeInfo:(NSInteger)number
{
    
    //    创建删除知识信息的sql语句
    NSString * deleteSql = [NSString stringWithFormat:@"DELETE FROM knowledgeList WHERE number = '%ld'",number];
    //    执行sql语句
    int result = sqlite3_exec(db1, [deleteSql UTF8String], NULL, NULL, NULL);
    if (result != SQLITE_OK) {
        //NSLog(@"删除失败");
    }
}


#pragma mark- 查询所有知识信息
-(NSMutableArray *)selectKnowledge
{
    
    //    创建查询知识信息的sql语句
    NSString * selectSql = @"SELECT * FROM knowledgeList";
    //    创建数据库指针对象
    sqlite3_stmt * stmt = nil;
    //   将数据库对象、SQL语句、指针对象关联在一起
    /*
     参数1: 数据库对象
     参数2: 查询语句
     参数3: 查询语句字数限制（－1为不限制）
     参数4: 数据库指针对象
     参数5: nil
     */
    int result = sqlite3_prepare_v2(db1, [selectSql UTF8String], -1, &stmt, nil);
    
    //    创建存放知识信息的大数组
    NSMutableArray * array = [[NSMutableArray alloc]init];
    
    if (result != SQLITE_OK) {
        //NSLog(@"查询失败");
    }
    //    循环遍历查询数据库表格每一行
    while (sqlite3_step(stmt) == SQLITE_ROW) {
        //        取出每一列数据
        int number = sqlite3_column_int(stmt, 0);

        
        const unsigned char * title = sqlite3_column_text(stmt, 1);
        const unsigned char * img = sqlite3_column_text(stmt, 2);
        const unsigned char * content = sqlite3_column_text(stmt, 3);
        const unsigned char * code = sqlite3_column_text(stmt, 4);
        const unsigned char * html = sqlite3_column_text(stmt, 5);
        
    
        NSString * titleString = [NSString stringWithUTF8String:(const char *)title];
        NSString * imgString = [NSString stringWithUTF8String:(const char *)img];
        NSString * contentString = [NSString stringWithUTF8String:(const char *)content];
        NSString * codeString = [NSString stringWithUTF8String:(const char *)code];
        NSString * htmlString = [NSString stringWithUTF8String:(const char *)html];
        
        
        KnowledgeModel *koo = [[KnowledgeModel alloc]init];
        koo.number = number;
        koo.title = titleString;
        koo.content = contentString;
        koo.code = codeString;
        koo.html = htmlString;
        koo.img = imgString;
        //        将菜谱信息model类存入大数组
        [array addObject:koo];
    }
    sqlite3_finalize(stmt);
    return array;
}


#pragma mark- 关闭数据库
-(void)closeKnowledgeDB
{
    
    int result = sqlite3_close(db1);
    db1 = nil;
    if (result != SQLITE_OK) {
        //NSLog(@"关闭数据库");
    }
}
















@end
